﻿using System;
using System.Collections;
using System.Text;
using System.Reflection;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.ComponentModel;

namespace Vehicle.DAL
{
    /// <summary>
    /// Copyright (C) M-Tear团队
    /// 文 件 名：SqlHelper.cs
    /// 版    本：v1.00.0000
    /// 创建标识：2009-05-4    Created by 杨光远 V1.00.0000
    /// 功能说明：Sql Server 数据库基类（只针对存储过程）
    /// 注意事项：无
    /// 
    /// 更新记录：
    /// 
    /// </summary>
    public class SqlHelper
    {
        #region ---私有变量---
        private static string connectionString = Vehicle.Common.WebConfig.ConnectionString;//获取连接字符串
        private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());
        #endregion

        #region ---执行存储过程操作---

        #region ---执行存储过程,返回受影响函数---
        /// <summary>
        /// 执行单条存储过程,返回受影响函数
        /// </summary>
        /// <param name="storedProcName">存储过程名称</param>
        /// <param name="ClassType">参数数组</param>
        /// <returns>返回受影响行数</returns>
        public static int ExecuteNonQuery(string storedProcName, params object[] parameterValues)
        {
            //判断存储过程名称是否合法
            if ((storedProcName != null) && (storedProcName.Length > 0))
            {
                //判断参数数组是否合法
                if ((parameterValues != null) && (parameterValues.Length > 0))
                {
                    SqlParameter[] _SqlParameter = GetSpParameters(storedProcName);//获取系统存储过程参数数组
                    AssignParameterValues(_SqlParameter, parameterValues);//参数赋值
                    return ExecuteNonQuery(storedProcName, CommandType.StoredProcedure, _SqlParameter);//执行有参数存储过程
                }
                else
                {
                    return ExecuteNonQuery(storedProcName, CommandType.StoredProcedure);//执行无参数存储过程
                }
            }
            else
            {
                throw new Exception("存储过程名称为空");
            }

        }
        /// <summary>
        /// 执行无返回值的无参数存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名称</param>
        /// <param name="_CommandTye">Command类型</param>
        /// <returns>返回受影响行数</returns>
        private static int ExecuteNonQuery(string storedProcName, CommandType _CommandTye)
        {
            int result = -1;
            using (SqlConnection _SqlConnection = new SqlConnection(connectionString))
            {
                _SqlConnection.Open();
                SqlCommand _SqlCommand = new SqlCommand(storedProcName, _SqlConnection);
                _SqlCommand.CommandType = _CommandTye;
                try
                {
                    result = _SqlCommand.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                _SqlCommand.Parameters.Clear();
                _SqlCommand.Dispose();
            }
            return result;
        }
        /// <summary>
        /// 执行无返回值的存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名称</param>
        /// <param name="_CommandTye">Command类型</param>
        /// <param name="_SqlParameter">存储过程参数数组</param>
        /// <returns>返回受影响行数</returns>
        private static int ExecuteNonQuery(string storedProcName, CommandType _CommandTye, params SqlParameter[] _SqlParameter)
        {
            int result = -1;
            using (SqlConnection _SqlConnection = new SqlConnection(connectionString))
            {
                _SqlConnection.Open();
                SqlCommand _SqlCommand = new SqlCommand(storedProcName, _SqlConnection);
                _SqlCommand.CommandType = _CommandTye;
                if (_SqlParameter != null)
                {
                    foreach (SqlParameter p in _SqlParameter)
                    {
                        _SqlCommand.Parameters.Add(p);
                    }
                }

                try
                {
                    result = _SqlCommand.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                _SqlCommand.Parameters.Clear();
                _SqlCommand.Dispose();
            }
            return result;
        }

        #endregion

        #region ---执行存储过程,返回DataTable---

        /// <summary>
        /// 执行存储过程,返回DataTable
        /// </summary>
        /// <param name="storedProcName">存储过程名称</param>
        /// <param name="parameterValues">参数数组</param>
        /// <returns></returns>
        public static DataTable GetDataTable(string storedProcName, params object[] parameterValues)
        {
            //判断存储过程名称是否合法
            if ((storedProcName != null) && (storedProcName.Length > 0))
            {
                //判断参数数组是否合法
                if ((parameterValues != null) && (parameterValues.Length > 0))
                {
                    SqlParameter[] _SqlParameter = GetSpParameters(storedProcName);//获取系统存储过程参数数组
                    AssignParameterValues(_SqlParameter, parameterValues);//参数赋值
                    return GetDataTable(storedProcName, CommandType.StoredProcedure, _SqlParameter);
                }
                else
                {
                    return GetDataTable(storedProcName, CommandType.StoredProcedure);
                }
            }
            else
            {
                throw new Exception("存储过程名称为空");
            }
        }
        /// <summary>
        /// 执行有参数存储过程返回DataTable
        /// </summary>
        /// <param name="storedProcName">存储过程名称</param>
        /// <param name="_CommandTye">Command类型</param>
        /// <returns></returns>
        private static DataTable GetDataTable(string storedProcName, CommandType _CommandTye, params SqlParameter[] _SqlParameter)
        {
            DataTable dt = new DataTable();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(storedProcName, connection);
                cmd.CommandType = _CommandTye;
                if (_SqlParameter != null)
                {
                    foreach (SqlParameter p in _SqlParameter)
                    {
                        cmd.Parameters.Add(p);
                    }
                }
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                sda.Fill(dt);
                cmd.Parameters.Clear();
                cmd.Dispose();
                sda.Dispose();
            }
            return dt;
        }
        /// <summary>
        /// 执行无参数存储过程返回DataTable
        /// </summary>
        /// <param name="storedProcName">存储过程名称</param>
        /// <param name="_CommandTye">Command类型</param>
        /// <param name="_SqlParameter">参数数组</param>
        /// <returns></returns>
        private static DataTable GetDataTable(string storedProcName, CommandType _CommandTye)
        {
            DataTable dt = new DataTable();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(storedProcName, connection);
                cmd.CommandType = _CommandTye;
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                sda.Fill(dt);
                cmd.Parameters.Clear();
                cmd.Dispose();
                sda.Dispose();
            }
            return dt;
        }
        #endregion

        #region ---执行存储过程返回DataSet--

        /// <summary>
        /// 执行存储过程,返回DataSet
        /// </summary>
        /// <param name="storedProcName">存储过程名称</param>
        /// <param name="parameterValues">参数数组</param>
        /// <returns></returns>
        public static DataSet GetDataSet(string storedProcName, params object[] parameterValues)
        {
            //判断存储过程名称是否合法
            if ((storedProcName != null) && (storedProcName.Length > 0))
            {
                //判断参数数组是否为空
                if ((parameterValues != null) && (parameterValues.Length > 0))
                {
                    SqlParameter[] _SqlParameter = GetSpParameters(storedProcName);//获取系统存储过程参数数组
                    AssignParameterValues(_SqlParameter, parameterValues);//参数赋值
                    return GetDataSet(storedProcName, CommandType.StoredProcedure, _SqlParameter);
                }
                else
                {
                    return GetDataSet(storedProcName, CommandType.StoredProcedure);//执行无参数返回DataSet
                }
            }
            else
            {
                throw new Exception("存储过程名称为空");
            }
        }
        /// <summary>
        /// 执行无参数存储过程返回DataSet
        /// </summary>
        /// <param name="storedProcName">存储过程名称</param>
        /// <param name="_CommandTye">参数数组</param>
        /// <returns></returns>
        private static DataSet GetDataSet(string storedProcName, CommandType _CommandTye)
        {
            DataSet ds = new DataSet();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(storedProcName, connection);
                cmd.CommandType = _CommandTye;
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                sda.Fill(ds);
                cmd.Parameters.Clear();
                cmd.Dispose();
                sda.Dispose();
            }
            return ds;

        }

        /// <summary>
        /// 执行有参数存储过程返回DataSet
        /// </summary>
        /// <param name="storedProcName">存储过程名称</param>
        /// <param name="_CommandTye">Command类型</param>
        /// <param name="_SqlParameter">参数数组</param>
        /// <returns></returns>
        private static DataSet GetDataSet(string storedProcName, CommandType _CommandTye, params SqlParameter[] _SqlParameter)
        {
            DataSet ds = new DataSet();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(storedProcName, connection);
                cmd.CommandType = _CommandTye;
                if (_SqlParameter != null)
                {
                    foreach (SqlParameter p in _SqlParameter)
                    {
                        cmd.Parameters.Add(p);
                    }
                }
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                sda.Fill(ds);
                cmd.Parameters.Clear();
                cmd.Dispose();
                sda.Dispose();
            }
            return ds;

        }
        #endregion

        #region ---执行存储过程返回SqlDataReader---
        /// <summary>
        /// 执行存储过程返回SqlDataReader
        /// </summary>
        /// <param name="storedProcName">存储过程名称</param>
        /// <param name="parameterValues">参数对象数组</param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(string storedProcName, params object[] parameterValues)
        {
            //判断存储过程名称是否合法
            if ((storedProcName != null) && (storedProcName.Length > 0))
            {
                //判断参数数组是否为空
                if ((parameterValues != null) && (parameterValues.Length > 0))
                {
                    SqlParameter[] _SqlParameter = GetSpParameters(storedProcName);//获取系统存储过程参数数组
                    AssignParameterValues(_SqlParameter, parameterValues);//参数赋值
                    return ExecuteReader(storedProcName, CommandType.StoredProcedure, _SqlParameter);
                }
                else
                {
                    return ExecuteReader(storedProcName, CommandType.StoredProcedure);//执行无参数返回SqlDataReader
                }
            }
            else
            {
                throw new Exception("存储过程名称为空");
            }
        }
        /// <summary>
        /// 执行有参数存储过程返回SqlDataReader
        /// </summary>
        /// <param name="storedProcName">存储过程名称</param>
        /// <param name="_CommandTye">Command类型</param>
        /// <param name="_SqlParameter">参数数组</param>
        /// <returns></returns>
        private static SqlDataReader ExecuteReader(string storedProcName, CommandType _CommandTye, params SqlParameter[] _SqlParameter)
        {

            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(storedProcName, connection);
            cmd.CommandType = _CommandTye;
            SqlDataReader returnReader;
            if (_SqlParameter != null)
            {
                foreach (SqlParameter p in _SqlParameter)
                {
                    cmd.Parameters.Add(p);
                }
            }
            connection.Open();//调用方法后一定要关闭
            returnReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);//SqlDataReader关闭后自动关闭connection链接
            return returnReader;
        }
        /// <summary>
        /// 执行无参数存储过程返回SqlDataReader
        /// </summary>
        /// <param name="storedProcName">存储过程名称</param>
        /// <param name="_CommandTye">Command类型</param>
        /// <returns></returns>
        private static SqlDataReader ExecuteReader(string storedProcName, CommandType _CommandTye)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(storedProcName, connection);
            cmd.CommandType = _CommandTye;
            SqlDataReader returnReader;
            connection.Open();//调用方法后一定要关闭
            returnReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);//SqlDataReader关闭后自动关闭connection链接
            return returnReader;
        }


        #endregion

        #region ---获取存储过程参数数组---

        /// <summary>
        /// 获取数据库中存储过程参数数组
        /// </summary>
        /// <param name="storedProcName">存储过程名称</param>
        /// <returns>返回存储过程参数数组</returns>
        private static SqlParameter[] GetSpParameters(string storedProcName)
        {
            string hashKey = "MTear" + ":" + storedProcName;
            SqlParameter[] cachedParameters;
            cachedParameters = paramCache[hashKey] as SqlParameter[];//取存储过程参数,先从静态变量(Hashtable)取, 如果没有找到, 则从数据库取
            if (cachedParameters == null)
            {
                SqlParameter[] spParameters = DiscoverSpParameters(storedProcName);
                paramCache[hashKey] = spParameters;
                cachedParameters = spParameters;
            }
            return CloneParameters(cachedParameters);
        }

        /// <summary>
        /// 从数据库取存储过程的参数
        /// </summary>
        /// <param name="strSpName">存储过程名</param>
        /// <returns>参数集合</returns>
        private static SqlParameter[] DiscoverSpParameters(string strSpName)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = new SqlCommand(strSpName, connection);
                command.CommandType = CommandType.StoredProcedure;
                SqlCommandBuilder.DeriveParameters(command);
                SqlParameter[] discoveredParameters = new SqlParameter[command.Parameters.Count];
                command.Parameters.CopyTo(discoveredParameters, 0);
                return discoveredParameters;
            }
        }

        /// <summary>
        /// 复制采集存储过程数组
        /// </summary>
        /// <param name="originalParameters">原参数集</param>
        /// <returns>目标参数</returns>
        private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters)
        {
            SqlParameter[] clonedParameters = new SqlParameter[originalParameters.Length];

            for (int i = 0, j = originalParameters.Length; i < j; i++)
            {
                clonedParameters[i] = (SqlParameter)((ICloneable)originalParameters[i]).Clone();
            }

            return clonedParameters;
        }
        #endregion

        #region ---将参数值序列赋值给对应的参数序列---

        /// <summary> 
        /// 将参数值序列赋值给对应的参数序列 
        /// </summary> 
        /// <param name="commandParameters">待赋值的参数序列</param> 
        /// <param name="parameterValues">参数值序列</param> 
        private static bool AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
        {
            bool ret = true;
            if ((commandParameters == null) || (parameterValues == null))
            {
                //若参数为null，返回 
                return ret;
            }
            // 参数数组长度与参数值数组长度需匹配 
            if (commandParameters.Length != parameterValues.Length)
            {
                ret = false;
                return ret;
            }
            //循环为参数赋值 
            //注意，参数与参数值在各自的序列中位置应当对应 
            for (int i = 0, j = commandParameters.Length; i < j; i++)
            {
                commandParameters[i].Value = parameterValues[i];
            }
            return ret;
        }
        #endregion


        #endregion
    }
}
